from sqlalchemy.orm import Session, aliased
from sqlalchemy import func, and_, desc
from typing import List, Dict, Optional
from datetime import datetime, timedelta
from app.models.flight import Flight
from app.models.booking import Booking, BookingStatus
from app.models.airport import Airport

Airport_2 = aliased(Airport)

class ReportService:
    def __init__(self, db: Session):
        self.db = db

    def get_flight_load_rate_stats(self, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None) -> List[Dict]:
        """统计各航班的载客率"""
        query = self.db.query(
            Flight.id,
            Flight.flight_number,
            Flight.departure_time,
            Flight.arrival_time,
            Flight.total_seats,
            Flight.available_seats,
            Airport.code.label("departure_airport"),
            Airport_2.code.label("arrival_airport")
        ).join(
            Airport, Flight.departure_airport_id == Airport.id
        ).join(
            Airport_2, Flight.arrival_airport_id == Airport_2.id
        )
        
        if start_date and end_date:
            query = query.filter(
                and_(
                    Flight.departure_time >= start_date,
                    Flight.departure_time <= end_date
                )
            )
        
        flights = query.all()
        
        result = []
        for flight in flights:
            # 计算已预订座位数
            booked_seats = self.db.query(func.count(Booking.id)).filter(
                and_(
                    Booking.flight_id == flight.id,
                    Booking.booking_status == BookingStatus.CONFIRMED
                )
            ).scalar() or 0
            
            total_seats = flight.total_seats or 100  # 默认值
            available_seats = flight.available_seats or total_seats
            
            load_rate = (booked_seats / total_seats) * 100 if total_seats > 0 else 0
            
            result.append({
                "flight_id": flight.id,
                "flight_number": flight.flight_number,
                "departure_airport": flight.departure_airport,
                "arrival_airport": flight.arrival_airport,
                "departure_time": flight.departure_time,
                "total_seats": total_seats,
                "booked_seats": booked_seats,
                "available_seats": available_seats,
                "load_rate": round(load_rate, 2)
            })
        
        return result

    def get_revenue_stats(self, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None, group_by: str = "flight") -> List[Dict]:
        """按航班或日期统计收入"""
        
        query = self.db.query(
            Flight.id,
            Flight.flight_number,
            Flight.departure_time,
            Airport.code.label("departure_airport"),
            Airport_2.code.label("arrival_airport")
        ).join(
            Airport, Flight.departure_airport_id == Airport.id
        ).join(
            Airport_2, Flight.arrival_airport_id == Airport_2.id
        )
        
        if start_date and end_date:
            query = query.filter(
                and_(
                    Flight.departure_time >= start_date,
                    Flight.departure_time <= end_date
                )
            )
        
        flights = query.all()
        
        result = []
        for flight in flights:
            # 计算该航班的收入
            revenue = self.db.query(func.sum(Booking.price)).filter(
                and_(
                    Booking.flight_id == flight.id,
                    Booking.booking_status == BookingStatus.CONFIRMED
                )
            ).scalar() or 0
            
            booking_count = self.db.query(func.count(Booking.id)).filter(
                and_(
                    Booking.flight_id == flight.id,
                    Booking.booking_status == BookingStatus.CONFIRMED
                )
            ).scalar() or 0
            
            if group_by == "flight":
                result.append({
                    "flight_id": flight.id,
                    "flight_number": flight.flight_number,
                    "departure_airport": flight.departure_airport,
                    "arrival_airport": flight.arrival_airport,
                    "departure_time": flight.departure_time,
                    "total_revenue": float(revenue),
                    "booking_count": booking_count,
                    "average_price": float(revenue / booking_count) if booking_count > 0 else 0
                })
            elif group_by == "date":
                # 按日期分组
                date_key = flight.departure_time.date()
                existing_entry = next((item for item in result if item["date"] == date_key), None)
                
                if existing_entry:
                    existing_entry["total_revenue"] += float(revenue)
                    existing_entry["booking_count"] += booking_count
                else:
                    result.append({
                        "date": date_key,
                        "total_revenue": float(revenue),
                        "booking_count": booking_count,
                        "average_price": float(revenue / booking_count) if booking_count > 0 else 0
                    })
        
        if group_by == "date":
            result.sort(key=lambda x: x["date"])
        
        return result

    def get_popular_routes(self, start_date: Optional[datetime] = None, end_date: Optional[datetime] = None, limit: int = 10) -> List[Dict]:
        """分析热门航线数据"""
        
        query = self.db.query(
            Airport.code.label("departure_airport"),
            Airport.name.label("departure_airport_name"),
            Airport.city.label("departure_city"),
            Airport_2.code.label("arrival_airport"),
            Airport_2.name.label("arrival_airport_name"),
            Airport_2.city.label("arrival_city"),
            func.count(Booking.id).label("booking_count"),
            func.sum(Booking.price).label("total_revenue")
        ).select_from(Booking).join(
            Flight, Booking.flight_id == Flight.id
        ).join(
            Airport, Flight.departure_airport_id == Airport.id
        ).join(
            Airport_2, Flight.arrival_airport_id == Airport_2.id
        ).filter(
            Booking.booking_status == BookingStatus.CONFIRMED
        ).group_by(
            Airport.code,
            Airport.name,
            Airport.city,
            Airport_2.code,
            Airport_2.name,
            Airport_2.city
        )
        
        if start_date and end_date:
            query = query.filter(
                and_(
                    Flight.departure_time >= start_date,
                    Flight.departure_time <= end_date
                )
            )
        
        routes = query.order_by(desc("booking_count")).limit(limit).all()
        
        result = []
        for route in routes:
            result.append({
                "route": f"{route.departure_airport} → {route.arrival_airport}",
                "departure_airport": {
                    "code": route.departure_airport,
                    "name": route.departure_airport_name,
                    "city": route.departure_city
                },
                "arrival_airport": {
                    "code": route.arrival_airport,
                    "name": route.arrival_airport_name,
                    "city": route.arrival_city
                },
                "booking_count": route.booking_count,
                "total_revenue": float(route.total_revenue or 0),
                "average_price": float(route.total_revenue / route.booking_count) if route.booking_count > 0 else 0
            })
        
        return result

    def get_daily_booking_trend(self, days: int = 30) -> List[Dict]:
        """获取每日预订趋势"""
        
        end_date = datetime.now()
        start_date = end_date - timedelta(days=days)
        
        daily_stats = self.db.query(
            func.date(Booking.booking_date).label("date"),
            func.count(Booking.id).label("booking_count"),
            func.sum(Booking.price).label("total_revenue")
        ).filter(
            and_(
                Booking.booking_date >= start_date,
                Booking.booking_date <= end_date,
                Booking.booking_status == BookingStatus.CONFIRMED
            )
        ).group_by(
            func.date(Booking.booking_date)
        ).order_by(
            func.date(Booking.booking_date)
        ).all()
        
        result = []
        for stat in daily_stats:
            result.append({
                "date": str(stat.date),
                "booking_count": stat.booking_count,
                "total_revenue": float(stat.total_revenue or 0)
            })
        
        return result

    def get_monthly_revenue_summary(self, months: int = 12) -> List[Dict]:
        """获取月度收入汇总"""
        
        monthly_stats = self.db.query(
            func.date_trunc('month', Booking.booking_date).label("month"),
            func.count(Booking.id).label("booking_count"),
            func.sum(Booking.price).label("total_revenue")
        ).filter(
            and_(
                Booking.booking_status == BookingStatus.CONFIRMED,
                Booking.booking_date >= datetime.now() - timedelta(days=months*30)
            )
        ).group_by(
            func.date_trunc('month', Booking.booking_date)
        ).order_by(
            func.date_trunc('month', Booking.booking_date)
        ).all()
        
        result = []
        for stat in monthly_stats:
            result.append({
                "month": str(stat.month)[:7],  # YYYY-MM format
                "booking_count": stat.booking_count,
                "total_revenue": float(stat.total_revenue or 0)
            })
        
        return result